#!/usr/bin/env python
"""
修复课程分配问题
@file admin/fix_course_assignment.py
@description 修复金融班级被错误分配计算机课程的问题
@created 2024-07-25
"""

import os
import django

# 设置Django环境
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'ai_teach.settings')
django.setup()

from django.db import connection
import logging

# 设置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

def fix_course_assignment():
    """
    修复课程分配问题，确保金融班级不再被分配计算机课程
    """
    try:
        with connection.cursor() as cursor:
            # 查找金融班级被分配计算机课程的记录
            cursor.execute("""
                SELECT tr.id, c.name as course_name, sc.name as class_name,
                       t.teacher_name, col_t.name as college_name
                FROM teaching_record tr
                JOIN courses c ON tr.course_id = c.course_id
                JOIN teacher t ON tr.teacher_id = t.teacher_id
                JOIN colleges col_t ON t.college_id = col_t.id
                JOIN school_classes sc ON tr.class_id = sc.id
                WHERE (sc.name LIKE '%经济%' OR sc.name LIKE '%金融%' OR sc.name LIKE '%贸易%')
                AND (c.name LIKE '%数据结构%' OR c.name LIKE '%计算机%' OR c.name LIKE '%软件%' 
                     OR c.name LIKE '%操作系统%' OR c.name LIKE '%网络%' OR c.name LIKE '%数据库%')
                AND tr.status = 1
            """)
            
            mismatched_records = cursor.fetchall()
            logger.info(f"找到 {len(mismatched_records)} 条金融班级被分配计算机课程的记录")
            
            # 显示这些记录
            for record in mismatched_records:
                record_id, course_name, class_name, teacher_name, college_name = record
                logger.info(f"ID: {record_id}, 课程: {course_name}, 班级: {class_name}, 教师: {teacher_name}, 学院: {college_name}")
            
            # 删除这些错误的记录
            if mismatched_records:
                for record in mismatched_records:
                    record_id = record[0]
                    cursor.execute("DELETE FROM teaching_record WHERE id = %s", [record_id])
                
                logger.info(f"已删除 {len(mismatched_records)} 条错误的课程分配记录")
            
            # 检查其他学院是否也有类似的错误分配
            college_course_patterns = {
                "计算机科学与技术学院": ["数据结构", "计算机组成", "操作系统", "计算机网络", "数据库", "软件工程", "人工智能", "算法"],
                "法学院": ["法学", "宪法", "民法", "刑法"],
                "经济与金融学院": ["经济", "金融", "贸易", "财务", "统计", "会计"],
                "建筑工程学院": ["数学", "代数", "概率", "物理", "力学"],
                "管理学院": ["管理", "市场", "人力资源", "组织行为"]
            }
            
            # 获取所有不匹配的记录
            all_mismatched = []
            
            cursor.execute("""
                SELECT tr.id, c.name as course_name, sc.name as class_name,
                       t.teacher_id, t.teacher_name, col_t.id as college_id, col_t.name as college_name
                FROM teaching_record tr
                JOIN courses c ON tr.course_id = c.course_id
                JOIN teacher t ON tr.teacher_id = t.teacher_id
                JOIN colleges col_t ON t.college_id = col_t.id
                JOIN school_classes sc ON tr.class_id = sc.id
                WHERE tr.status = 1
            """)
            
            all_records = cursor.fetchall()
            for record in all_records:
                record_id, course_name, class_name, teacher_id, teacher_name, college_id, college_name = record
                
                # 检查课程名称是否与学院匹配
                is_matched = False
                for college, course_keywords in college_course_patterns.items():
                    if college == college_name:  # 教师属于这个学院
                        if any(keyword in course_name for keyword in course_keywords):
                            is_matched = True
                            break
                
                if not is_matched:
                    all_mismatched.append(record)
                    logger.warning(f"不匹配: 课程 '{course_name}' 由学院 '{college_name}' 的教师 {teacher_name} 教授")
            
            logger.info(f"找到 {len(all_mismatched)} 条教师与课程不匹配的教学记录")
            
            # 删除这些错误的记录
            if all_mismatched:
                for record in all_mismatched:
                    record_id = record[0]
                    cursor.execute("DELETE FROM teaching_record WHERE id = %s", [record_id])
                
                logger.info(f"已删除 {len(all_mismatched)} 条错误的教师课程分配记录")
            
            # 检查班级与课程的关联是否正确
            class_mismatched = []
            
            cursor.execute("""
                SELECT tr.id, c.name as course_name, sc.name as class_name,
                       col_c.id as course_college_id, col_c.name as course_college,
                       t.teacher_name
                FROM teaching_record tr
                JOIN courses c ON tr.course_id = c.course_id
                JOIN teacher t ON tr.teacher_id = t.teacher_id
                JOIN colleges col_c ON c.college_id = col_c.id
                JOIN school_classes sc ON tr.class_id = sc.id
                WHERE tr.status = 1
            """)
            
            class_records = cursor.fetchall()
            for record in class_records:
                record_id, course_name, class_name, course_college_id, course_college, teacher_name = record
                
                # 检查班级名称是否与课程学院匹配
                is_matched = False
                class_patterns = {
                    "计算机科学与技术学院": ["计算机", "软件", "人工智能", "网络"],
                    "法学院": ["法学", "国际法"],
                    "经济与金融学院": ["经济", "金融", "贸易"],
                    "建筑工程学院": ["工程", "建筑"],
                    "管理学院": ["管理", "会计"]
                }
                
                college_patterns = class_patterns.get(course_college, [])
                if any(pattern in class_name for pattern in college_patterns):
                    is_matched = True
                
                if not is_matched:
                    class_mismatched.append(record)
                    logger.warning(f"不匹配: '{course_college}' 的课程 '{course_name}' 分配给班级 '{class_name}'")
            
            logger.info(f"找到 {len(class_mismatched)} 条课程与班级不匹配的教学记录")
            
            # 删除这些错误的记录
            if class_mismatched:
                for record in class_mismatched:
                    record_id = record[0]
                    cursor.execute("DELETE FROM teaching_record WHERE id = %s", [record_id])
                
                logger.info(f"已删除 {len(class_mismatched)} 条错误的课程班级分配记录")
            
            logger.info("课程分配问题修复完成")
            
    except Exception as e:
        logger.error(f"修复课程分配问题失败: {str(e)}")
        import traceback
        traceback.print_exc()

if __name__ == "__main__":
    fix_course_assignment() 